﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using Csla.Data;
using Sireass.Dal;
using log4net;

namespace Sireass.DalSql
{
	public class ProgramaDal : IProgramaDal
	{
		// Create a logger for use in this class
		private static readonly ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
		private SqlParameter paramAccion = null;
		private string nombreProcedimiento = null;

		public ProgramaDal()
		{
			paramAccion = new SqlParameter("@Accion", System.Data.SqlDbType.SmallInt);
			nombreProcedimiento = "spSireassCatPrograma";
		}

		public System.Data.IDataReader Fetch()
		{
			using (var ctx = ConnectionManager<SqlConnection>.GetManager("SqlServer"))
			{
				var cm = ctx.Connection.CreateCommand();
				cm.CommandType = System.Data.CommandType.StoredProcedure;
				cm.CommandText = nombreProcedimiento;
				cm.Parameters.AddWithValue("@Accion", AccionDal.AccionSP.CONSULTAR);
				return cm.ExecuteReader();
			}
		}

		public System.Data.IDataReader Fetch(long idPrograma)
		{
			using (var ctx = ConnectionManager<SqlConnection>.GetManager("SqlServer"))
			{
				var cm = ctx.Connection.CreateCommand();
				cm.CommandType = System.Data.CommandType.StoredProcedure;
				cm.CommandText = nombreProcedimiento;
				cm.Parameters.AddWithValue("@Accion", AccionDal.AccionSP.CONSULTAR);
				cm.Parameters.AddWithValue("@nId_Programa", idPrograma);
				if (log.IsDebugEnabled)
				{
					//log.Debug("parametro Accion: " + cm.Parameters[0].ParameterName);
					//log.Debug("parametro Accion-Valor: " + cm.Parameters[0].Value);
					//log.Debug("parametro nId_Programa: " + cm.Parameters[1].ParameterName);
					//log.Debug("parametro nId_Programa-Valor: " + cm.Parameters[1].Value);
					foreach (SqlParameter param in cm.Parameters)
					{						
						log.Debug(param.ParameterName + ": " + param.Value);
					}
				}
				return cm.ExecuteReader();
			}
		}

        public System.Data.IDataReader Fetch(long? idPrograma, long? idEscuela)
        {
            using (var ctx = ConnectionManager<SqlConnection>.GetManager("SqlServer"))
            {
                var cm = ctx.Connection.CreateCommand();
                cm.CommandType = System.Data.CommandType.StoredProcedure;
                cm.CommandText = nombreProcedimiento;
                cm.Parameters.AddWithValue("@Accion", AccionDal.AccionSP.CONSULTAR);
                if (idPrograma != null)
                    cm.Parameters.AddWithValue("@nId_Programa", idPrograma.Value);
                if (idEscuela != null)
                    cm.Parameters.AddWithValue("@nId_Escuela", idEscuela.Value);
				
                if (log.IsDebugEnabled)
                {
                                      foreach (SqlParameter param in cm.Parameters)
                    {
                        log.Debug(param.ParameterName + ": " + param.Value);
                    }
                }
                return cm.ExecuteReader();
            }
        }

		public long Insert(string clavePrograma, string programa, long idEscuela, DateTime fechaInicioVigencia, DateTime fechaFinVigencia, bool habilitado)
		{
			using (var ctx = ConnectionManager<SqlConnection>.GetManager("SqlServer"))
			{
				using (var cm = ctx.Connection.CreateCommand())
				{
					cm.CommandType = System.Data.CommandType.StoredProcedure;
					cm.CommandText = nombreProcedimiento;
					cm.Parameters.AddWithValue("@Accion", AccionDal.AccionSP.INSERTAR);
					cm.Parameters.AddWithValue("@cClave_Programa", clavePrograma);
					cm.Parameters.AddWithValue("@cPrograma", programa);
					cm.Parameters.AddWithValue("@nId_Escuela", idEscuela);
					cm.Parameters.AddWithValue("@dFecha_Inicio_Vigencia", fechaInicioVigencia);
					cm.Parameters.AddWithValue("@dFechaFin_Vigencia", fechaFinVigencia);
					cm.Parameters.AddWithValue("@bHabilitado", habilitado);
					SqlParameter paramIdPrograma = new SqlParameter("@nId_Programa", System.Data.SqlDbType.TinyInt);
					paramIdPrograma.Direction = System.Data.ParameterDirection.Output;
					cm.Parameters.Add(paramIdPrograma);
					if (log.IsDebugEnabled)
					{
						log.Debug("Insert");
						log.Debug("storedProcedure: " + nombreProcedimiento);
						//log.Debug("paramAccion: " + cm.Parameters["@Accion"].Value);
						//log.Debug("cPrograma: " + cm.Parameters["@cPrograma"].Value);
						foreach (SqlParameter param in cm.Parameters)
						{
							log.Debug(param.ParameterName + ": " + param.Value);
						}
					}
					cm.ExecuteNonQuery();
					if (log.IsDebugEnabled)
					{
						//log.Debug("nId_Programa: " + cm.Parameters["@nId_Programa"].Value);
						foreach (SqlParameter param in cm.Parameters)
						{
							if (param.Direction == System.Data.ParameterDirection.Output)
							{
								log.Debug(param.ParameterName + ": " + param.Value);
							}
						}
					}
					return Convert.ToInt64(paramIdPrograma.Value);
				}
			}
		}

		public void Update(long idPrograma, string clavePrograma, string programa, long idEscuela, DateTime fechaInicioVigencia, DateTime fechaFinVigencia, bool habilitado)
		{
			using (var ctx = ConnectionManager<SqlConnection>.GetManager("SqlServer"))
			{
				using (var cm = ctx.Connection.CreateCommand())
				{
					cm.CommandType = System.Data.CommandType.StoredProcedure;
					cm.CommandText = nombreProcedimiento;
					cm.Parameters.AddWithValue("@Accion", AccionDal.AccionSP.ACTUALIZAR);
					cm.Parameters.AddWithValue("@nId_Programa", idPrograma);
					cm.Parameters.AddWithValue("@cClave_Programa", clavePrograma);
					cm.Parameters.AddWithValue("@cPrograma", programa);
					cm.Parameters.AddWithValue("@nId_Escuela", idEscuela);
					cm.Parameters.AddWithValue("@dFecha_Inicio_Vigencia", fechaInicioVigencia);
					cm.Parameters.AddWithValue("@dFechaFin_Vigencia", fechaFinVigencia);
					cm.Parameters.AddWithValue("@bHabilitado", habilitado);
					if (log.IsDebugEnabled)
					{
						log.Debug("Update");
						log.Debug("storedProcedure: " + nombreProcedimiento);
						log.Debug("paramAccion: " + cm.Parameters["@Accion"].Value);
						log.Debug("nId_Programa: " + cm.Parameters["@nId_Programa"].Value);
					}
					var rowsAffected = cm.ExecuteNonQuery();
					if (log.IsDebugEnabled)
					{
						log.Debug("rowsAffected: " + rowsAffected);
					}
					if (rowsAffected <= 0)
						throw new DataNotFoundException("Programa");
				}
			}
		}

		public void Delete(long idPrograma)
		{
			using (var ctx = ConnectionManager<SqlConnection>.GetManager("SqlServer"))
			{
				using (var cm = ctx.Connection.CreateCommand())
				{
					cm.CommandType = System.Data.CommandType.StoredProcedure;
					cm.CommandText = nombreProcedimiento;
					cm.Parameters.AddWithValue("@Accion", AccionDal.AccionSP.ELIMINAR);
					cm.Parameters.AddWithValue("@nId_Programa", idPrograma);
					if (log.IsDebugEnabled)
					{
						log.Debug("Delete");
						log.Debug("storedProcedure: " + nombreProcedimiento);
						log.Debug("paramAccion: " + cm.Parameters["@Accion"].Value);
						log.Debug("nId_Programa: " + cm.Parameters["@nId_Programa"].Value);
					}
					var rowsAffected = cm.ExecuteNonQuery();
					if (log.IsDebugEnabled)
					{
						log.Debug("rowsAffected: " + rowsAffected);
					}
					if (rowsAffected <= 0)
						throw new DataNotFoundException("Programa");
				}
			}
		}
	}
}
